package com.sailfish.springbootdemo.dao.db2;

import com.sailfish.springbootdemo.common.DB2TableName;
import com.sailfish.springbootdemo.pojo.db2.PcbMaterialLibrary;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface PcbMaterialLibraryDao {
    @Results(id = "pcbMaterialLibraryResultMap", value = {
            @Result(column = "pcb_material_library_id", property = "pcbMaterialLibraryId", jdbcType = JdbcType.INTEGER),
            @Result(column = "row_key", property = "rowKey", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_key", property = "paramKey", jdbcType = JdbcType.VARCHAR),
            @Result(column = "param_value", property = "paramValue", jdbcType = JdbcType.VARCHAR),
            @Result(column = "value_type", property = "valueType", jdbcType = JdbcType.VARCHAR),
            @Result(column = "remark", property = "remark", jdbcType = JdbcType.VARCHAR),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "create_user", property = "createUser", jdbcType = JdbcType.VARCHAR),
            @Result(column = "update_user", property = "updateUser", jdbcType = JdbcType.VARCHAR),
    })
    /*
     * 单个查询
     */
    @Select("<script> " +
            "select *" +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            " where 1 = 1 " +
            "<if test='id != null'>" +
            " and pcb_material_library_id = #{id}" +
            "</if>" +
            " order by sort" +
            "</script>")
    List<PcbMaterialLibrary> getAllById(@Param("id") Integer id);

    @Select("<script> " +
            "select *" +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            " where 1 = 1 " +
            "<if test='paramKey != null'>" +
            " and param_key = #{paramKey}" +
            "</if>" +
            "<if test='rowKey != null'>" +
            " and row_key = #{rowKey}" +
            "</if>" +
            " order by sort" +
            "</script>")
    @ResultMap(value = "pcbMaterialLibraryResultMap")
    PcbMaterialLibrary getAllByRowKeyAndParamKey(@Param("paramKey") String paramKey, @Param("rowKey") String rowKey);

    /*
     * 查询指定行数据
     */
    @Select("<script> " +
            "select pcb_material_library_id, row_key, param_key, param_value, value_type, remark, create_time, create_user, update_time, update_user, status, sort " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
            "<if test='pcbMaterialLibraryId != null'>" +
            " and pcb_material_library_id = #{pcbMaterialLibraryId}" +
            "</if>" +
            "<if test='rowKey != null '>" +
            " and row_key = #{rowKey}" +
            "</if>" +
            "<if test='paramKey != null '>" +
            " and param_key = #{paramKey}" +
            "</if>" +
            "<if test='paramValue != null '>" +
            " and param_value like CONCAT('%',#{paramValue},'%') " +
            "</if>" +
            "<if test='valueType != null '>" +
            " and  value_type = #{valueType}" +
            "</if>" +
            "<if test='remark != null '>" +
            " and remark = #{remark}" +
            "</if>" +
            "<if test='status != null '>" +
            " and status = #{status}" +
            "</if>" +
            "<if test='sort != null '>" +
            " and sort = #{sort}" +
            "</if>" +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbMaterialLibraryResultMap")
    List<PcbMaterialLibrary> getAllByLimit(PcbMaterialLibrary pcbMaterial);

    //按字段模糊搜索
    @Select("<script> " +
            "select pcb_material_library_id, row_key, param_key, param_value, value_type, remark, create_time, create_user, update_time, update_user, status, sort " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
            "<if test='pcbMaterialLibraryId != null'>" +
            " and pcb_material_library_id = #{pcbMaterialLibraryId}" +
            "</if>" +
            "<if test='rowKey != null '>" +
            " and row_key = #{rowKey}" +
            "</if>" +
            "<if test='paramKey != null '>" +
            " and param_key = #{searchK}" +
            "</if>" +
            "<if test='paramValue != null '>" +
            " and param_value like CONCAT('%',#{searchV},'%') " +
            "</if>" +
            "<if test='valueType != null '>" +
            " and  value_type = #{valueType}" +
            "</if>" +
            "<if test='remark != null '>" +
            " and remark = #{remark}" +
            "</if>" +
            "<if test='status != null '>" +
            " and status = #{status}" +
            "</if>" +
            "<if test='sort != null '>" +
            " and sort = #{sort}" +
            "</if>" +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbMaterialLibraryResultMap")
    List<PcbMaterialLibrary> getAllBySearch(@Param("searchK")String searchK,@Param("searchV")String searchV);

    @Select("<script> " +
            "select distinct row_key " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
//            "<foreach collection=\"entities\" item=\"entity\" separator=\",\">" +
//            " and param_key = #{entity.paramKey} and param_value like concat('%',#{paramValue},'%')" +
//            "</foreach>" +
            "<if test='paramKey != null '>" +
            " and param_key = #{paramKey}" +
            "</if>" +
            "<if test='paramValue != null '>" +
            " and param_value like CONCAT('%',#{paramValue},'%') " +
            "</if>" +
            "</where>" +
            "</script>")
    List<String> getBySearch(PcbMaterialLibrary param);

    @Select("<script> " +
            "select distinct row_key " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
            "<if test='paramKey != null '>" +
            " and param_key = #{paramKey}" +
            "</if>" +
            "<if test='paramValue != null '>" +
            " and param_value >= #{paramValue}" +
            "</if>" +
            "</where>" +
            "</script>")
    List<String> getByParamValueThreshHold(@Param("paramKey") String paramKey, @Param("paramValue") Double paramValue);

    @Select("<script> " +
            "select * " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
            "row_key in " +
            "<if test = 'ids != null'>" +
            "<foreach collection=\"ids\" item = \"id\" open=\"(\" separator=\",\" close=\")\">  " +
            "#{id} " +
            "</foreach>" +
            "</if>" +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbMaterialLibraryResultMap")
    List<PcbMaterialLibrary> getByRowKey(@Param("ids") List<String> ids);

    /*
     *新增所有列
     */
    @Insert("insert into " + DB2TableName.PCB_MATERIAL_LIBRARY + " (row_key, param_key, param_value, value_type, remark, create_time, create_user, update_time, update_user, status, sort) values " +
            "(#{rowKey}, #{paramKey}, #{paramValue}, #{valueType}, #{remark}, #{createTime}, #{createUser}, #{updateTime}, #{updateUser}, #{status}, #{sort})")
    // 显式设置主键
    @Options(useGeneratedKeys = true, keyProperty = "pcbMaterialLibraryId")
    Integer insert(PcbMaterialLibrary param);

    /*
     *通过主键删除
     */
    @Delete("delete from " + DB2TableName.PCB_MATERIAL_LIBRARY + " where pcb_material_library_id = #{id} ")
    Integer deleteById(@Param("id") Integer id);

    /*
     *通过rowKey删除
     */
    @Delete("delete from " + DB2TableName.PCB_MATERIAL_LIBRARY + " where row_key = #{rowKey} ")
    Integer delete(@Param("rowKey") String rowKey);

    /*
     *通过主键修改数据
     */
    @Update("<script>" +
            "update " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<set> " +
            "<if test='rowKey != null'>" +
            "row_key = #{rowKey}," +
            "</if>" +
            "<if test='paramKey != null'>" +
            "param_key = #{paramKey}," +
            "</if>" +
            "<if test='paramValue != null '>" +
            "param_value = #{paramValue}," +
            "</if>" +
            "<if test='valueType != null '>" +
            "valueType = #{valueType}," +
            "</if>" +
            "<if test='remark != null and remark != \"\"'>" +
            "remark = #{remark}," +
            "</if>" +
            "<if test='createUser != null and createUser != \"\"'>" +
            "create_user = #{createUser}," +
            "</if>" +
            "<if test='createTime != null '>" +
            "create_time = #{createTime}," +
            "</if>" +
            "<if test='updateUser != null and updateUser != \"\"'>" +
            "update_user =#{updateUser}," +
            "</if>" +
            "<if test='updateTime != null '>" +
            "update_time = #{updateTime}," +
            "</if>" +
            "<if test='status != null '>" +
            "status = #{status}," +
            "</if>" +
            "<if test='sort != null '>" +
            "sort = #{sort} " +
            "</if>" +
            "</set> " +
            "where pcb_material_library_id = #{pcbMaterialLibraryId}" +
            "</script>")
    Integer update(PcbMaterialLibrary param);

    //查询板材和排序
    @Select("<script> " +
            "select pcb_material_library_id, row_key, param_key, param_value, value_type, remark, create_time, create_user, update_time, update_user, status, sort " +
            " from " + DB2TableName.PCB_MATERIAL_LIBRARY +
            "<where>" +
            "<if test='paramKey != null'>" +
            " and param_key = #{paramKey}" +
            "</if>" +
            " or param_key = 'input_sort'" +
            "</where>" +
            "</script>")
    @ResultMap(value = "pcbMaterialLibraryResultMap")
    List<PcbMaterialLibrary> getMaterial(@Param("paramKey")String paramKey);
}
